package quanlydoanra.func03;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import javax.faces.model.SelectItem;
import utils.UtilsOnScopes;
import dbutil.ConnectDB;
import exportExcel.ExportExcel;

/******************************************
 * Filename			: F03FuncInfo.java
 * Description		: Main bean for Search function of DOANRA
 * Created date		: Jul 15, 2008
 * Author			: Phonglt
 ******************************************/
public class F03FuncInfo {
	private String error;
	private String info;
	private int key;
	private String field;
	private String field2;
	private Date date1;
	private Date date2;
	private List<F03ObjInfoItem> list = new ArrayList<F03ObjInfoItem>();
	private F03KeySearch keySearch = new F03KeySearch();
	private ArrayList<HashMap<String, Object>> database = new ArrayList<HashMap<String,Object>>();
	private String linkDownload;
		
	/****************************************************
	 * Method name		: getError [GETTER]
	 * Return type		: F03FuncInfo
	 * Description		: 
	 * Created date		: Jul 19, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public String getError() {
		return error;
	}
	/****************************************************
	 * Method name		: setError [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 19, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setError(String error) {
		this.error = error;
	}
	/****************************************************
	 * Method name		: getInfo [GETTER]
	 * Return type		: F03FuncInfo
	 * Description		: 
	 * Created date		: Jul 19, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public String getInfo() {
		return info;
	}
	/****************************************************
	 * Method name		: setInfo [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 19, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setInfo(String info) {
		this.info = info;
	}
	/****************************************************
	 * Method name		: getField [GETTER]
	 * Return type		: F03FuncInfo
	 * Description		: 
	 * Created date		: Jul 19, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public String getField() {
		return field;
	}
	
	/****************************************************
	 * Method name		: getKey [GETTER]
	 * Return type		: F03FuncInfo
	 * Description		: 
	 * Created date		: Jul 19, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public int getKey() {
		return key;
	}
	/****************************************************
	 * Method name		: setKey [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 19, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setKey(int key) {
		this.key = key;
	}

	/****************************************************
	 * Method name		: setField [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 19, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setField(String field) {
		this.field = field;
	}
	/****************************************************
	 * Method name		: getField2 [GETTER]
	 * Return type		: F03FuncInfo
	 * Description		: 
	 * Created date		: Jul 19, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public String getField2() {
		return field2;
	}
	/****************************************************
	 * Method name		: setField2 [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 19, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setField2(String field2) {
		this.field2 = field2;
	}
	/****************************************************
	 * Method name		: getList [GETTER]
	 * Return type		: F03FuncInfo
	 * Description		: 
	 * Created date		: Jul 19, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public List<F03ObjInfoItem> getList() {
		this.setError("");
		this.setList(new ArrayList<F03ObjInfoItem>());
		int typeSearch = this.keySearch.getTypeSearch(this.key);
		String fieldSearch = this.keySearch.getFieldSearch(this.key);

		String SQL = "SELECT A.id, A.loaiDoan, A.nuocDen, A.ngayDi, A.ngayVe,"
				+ " A.donViMoi, C.linhVuc, C.mucDich, A.chiPhi, B.soqd, A.madoan, "
				+ "B.nguoikyqd, B.ngaykyqd, A.baocaokq "
				+ "FROM doanra A, quyetdinh B, mucdich C "
				+ "WHERE A.idmucdich = C.id AND A.idquyetdinh = B.id AND a.flagdelete = 0 ";
		System.out.println(typeSearch );
		switch (typeSearch) {
		case 1:
			if (this.field != null && !("".equals(this.field))) {
				this.setError("");
				SQL += " AND TRIM(LOWER(" + fieldSearch.trim()
						+ ")) like TRIM(LOWER('%" + this.field + "%'))";
				SQL += " ORDER BY " + fieldSearch + ", A.loaidoan ASC, A.ngayve DESC";
			} else 
				SQL += " AND 1 = 2 ";
			break;

		case 2://tim voi du lieu nga`y
			if (this.date1 != null && this.date2 != null
					&& this.date1.compareTo(this.date2) <= 0) {
				this.setError("");
				SQL += " AND " + fieldSearch + " BETWEEN '" + this.formatDate(this.date1) + "' AND '" + this.formatDate(this.date2) + "'";
				SQL += " ORDER BY " + fieldSearch + ", A.loaidoan ASC, A.ngayve DESC";
			}else {
				this.setError("Có lỗi! <br/>* Bạn đã chưa nhập ngày <br/>* Hoặc là ngày nhập không phù hợp.");
				this.setList(new ArrayList<F03ObjInfoItem>());
				return this.list;
			}
				
			break;

		case 3://tim theo giai doan[period][period = month]
			if (this.field != null && this.field2 != null && !("".equals(this.field))) {
				try {
					int year = Integer.parseInt(this.field);
					int period = Integer.parseInt(this.field2);
					if(period <= 12){
						SQL += " AND DATE_PART('year',ngaydi) = " + year + " AND DATE_PART('month',ngaydi) = " + period;
					} else {
						switch (period) {
						case 13://QUY I
							SQL += " AND ngaydi BETWEEN '" + year + "-01-01' AND '" + year + "-03-31'";
							break;
						case 14://QUY II
							SQL += " AND ngaydi BETWEEN '" + year + "-04-01' AND '" + year + "-06-30'";
							break;
						case 15://QUY III
							SQL += " AND ngaydi BETWEEN '" + year + "-07-01' AND '" + year + "-09-30'";
							break;
						case 16://QUY IV
							SQL += " AND ngaydi BETWEEN '" + year + "-10-01' AND '" + year + "-12-31'";
							break;
						case 17://6 thang DAU nam
							SQL += " AND ngaydi BETWEEN '" + year + "-01-01' AND '" + year + "-06-30'";
							break;
						case 18://6 thang CUOI nam
							SQL += " AND ngaydi BETWEEN '" + year + "-07-01' AND '" + year + "-12-31'";
							break;

						default:
							this.setError("Lỗi khi báo cáo theo giai đoạn.");
							break;
						}
					}
					SQL += " ORDER BY ngaydi DESC";
				}catch (Exception e) {
					this.setError("Có lỗi!");
				}
			}
			break;

		default:
			
			break;
		}
		System.out.println(SQL);
		ArrayList<HashMap<String, Object>> arr = ConnectDB.getDataTypeList(SQL);
		for (HashMap<String, Object> row : arr) {
			row.put("nguoitrongdoan", getMemberList((String) row.get("madoan")));
		}
		this.database = arr;//set lai de danh cho xuat file Exel
		if (arr.size() > 0) {
			this.setError("");
			List<F03ObjInfoItem> items = new ArrayList<F03ObjInfoItem>();

			for (int i = 0; i < arr.size(); i++) {
				HashMap<String, Object> row = arr.get(i);
				F03ObjInfoItem item = new F03ObjInfoItem(i + 1, (String) row
						.get("loaidoan"), (String) row.get("nuocden"),
						(Date) row.get("ngaydi"), (Date) row.get("ngayve"),
						(String) row.get("donvimoi"), (String) row
								.get("linhvuc"), (String) row.get("mucdich"),
						(String) row.get("chiphi"), (String) row.get("soqd"),
						(Date) row.get("ngaykyqd"), (String) row
								.get("nguoikyqd"), (Boolean) row.get(
								"baocaokq").equals(true) ? "Đã nộp" : "Chưa",
										((String) row.get("nguoitrongdoan")).replaceAll(System.getProperty("line.separator"), "<br/>"));
				items.add(item);
			}
			this.setList(items);
		}

		return list;
	}
	/***************************************************************************
	 * Method name : setList [SETTER] 
	 * Return type : void 
	 * Description : 
	 * Created date : Jul 19, 2008 
	 * Author 		: Phonglt
	 **************************************************************************/
	public void setList(List<F03ObjInfoItem> list) {
		this.list = list;
	}
	
	/****************************************************
	 * Method name		: getDate1 [GETTER]
	 * Return type		: F03FuncInfo
	 * Description		: 
	 * Created date		: Jul 26, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public Date getDate1() {
		return date1;
	}
	/****************************************************
	 * Method name		: setDate1 [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 26, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setDate1(Date date1) {
		this.date1 = date1;
	}
	/****************************************************
	 * Method name		: getDate2 [GETTER]
	 * Return type		: F03FuncInfo
	 * Description		: 
	 * Created date		: Jul 26, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public Date getDate2() {
		return date2;
	}
	/****************************************************
	 * Method name		: setDate2 [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 26, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setDate2(Date date2) {
		this.date2 = date2;
	}
	
	/****************************************************
	 * Method name		: getLinkDownload [GETTER]
	 * Return type		: F03FuncInfo
	 * Description		: 
	 * Created date		: Jul 27, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public String getLinkDownload() {
		return linkDownload;
	}
	/****************************************************
	 * Method name		: setLinkDownload [SETTER]
	 * Return type		: void
	 * Description		: 
	 * Created date		: Jul 27, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public void setLinkDownload(String linkDownload) {
		this.linkDownload = linkDownload;
	}
	/**************************************
	 * Method name		: search
	 * Return type		: String
	 * Description		: 
	 * Created date		: Jul 19, 2008
	 * Author			: Phonglt
	 **************************************/
	public String search(){
		this.getList();
		return "func03";
	}
	
	private String getMemberList(String idDoanRa) {
		try {
			String SQL = "SELECT DISTINCT A.tennguoidi FROM dsnguoidi A, doanra B WHERE A.iddoanra = B.madoan AND B.madoan = '" + idDoanRa +"'";
			ArrayList<HashMap<String, Object>> arr = ConnectDB.getDataTypeList(SQL);
			System.out.println(SQL);
			if(arr.size() > 0){
				StringBuilder ds = new StringBuilder();
				for(int i = 0; i < arr.size(); i++){
					HashMap<String, Object> row = arr.get(i);
					ds.append(((String)row.get("tennguoidi")).trim() + System.getProperty("line.separator"));
				}
				return ds.toString();
			} else {
				return "-";
			}

		} catch (Exception e) {
			this.setError("Không thể lấy danh sách người trong đoàn.");
		}
		return "-";
	}
	/**************************************
	 * Method name		: totalItem
	 * Return type		: int
	 * Description		: tong so cac item datim duoc
	 * Created date		: Jul 19, 2008
	 * Author			: Phonglt
	 **************************************/
	public int getTotalItem(){
		if(this.list == null) return 0;
		return this.list.size();
	}
	
	public String changeKey(){
		return "func03";
	}
	
	/**************************************
	 * Method name		: getListYear
	 * Return type		: SelectItem[]
	 * Description		: lay ra danh sach nam tu 1900 -> hien tai
	 * Created date		: Jul 20, 2008
	 * Author			: Phonglt
	 **************************************/
	@SuppressWarnings("deprecation")
	public SelectItem[] getListYear(){
		int curr = new Date(System.currentTimeMillis()).getYear() + 1900;
		int size = (curr - 1900) + 1;
		SelectItem[] listYear = new SelectItem[size];
		for (int i = 0; i < listYear.length; i++) {
			int currValue = (curr - i);
			listYear[i] = new SelectItem(currValue, String.valueOf(currValue));
		}
		return listYear;
	}
	
	/**************************************
	 * Method name		: getPeriod
	 * Return type		: SelectItem[]
	 * Description		: lay danh sach cac moc thoi gian bao cao
	 * Created date		: Jul 20, 2008
	 * Author			: Phonglt
	 **************************************/
	public SelectItem[] getPeriod(){
		SelectItem[] lott = new SelectItem[18];
		for (int i = 1; i <= 12; i++) {
			lott[i - 1] = new SelectItem(i, new String("Tháng ") + (i));
		}
		lott[12] = new SelectItem(13, "Quý I");
		lott[13] = new SelectItem(14, "Quý II");
		lott[14] = new SelectItem(15, "Quý III");
		lott[15] = new SelectItem(16, "Quý IV");
		lott[16] = new SelectItem(17, "6 tháng ĐẦU năm");
		lott[17] = new SelectItem(18, "6 tháng CUỐI năm");
		return lott;
	}
	
	/**************************************
	 * Method name		: getKeySearchItem
	 * Return type		: SelectItem[]
	 * Description		: lay cac tieu chi tim kiem
	 * Created date		: Jul 20, 2008
	 * Author			: Phonglt
	 **************************************/
	public SelectItem[] getKeySearchItem(){
		return this.keySearch.getKeySearchItem();
	}
	
	public String getSrcForm(){
		return this.keySearch.getSrcForm(this.key);
	}
	
	/**************************************
	 * Method name		: formatDate
	 * Return type		: String
	 * Description		: lay date ra dinh dang mm/DD/yyyy
	 * Created date		: Jul 26, 2008
	 * Author			: Phonglt
	 **************************************/
	@SuppressWarnings("deprecation")
	private String formatDate(Date date) {
		return  (date.getYear()+ 1900) + "-" +  (date.getMonth() + 1) + "-" + date.getDate();
	}
	
	/**************************************
	 * Method name		: reset
	 * Return type		: String
	 * Description		:
	 * Created date		: Jul 27, 2008
	 * Author			: Phonglt
	 **************************************/
	public String reset() {
		UtilsOnScopes.getSession().removeAttribute("f03FuncInfo");
		return "func03";
	}
	
	/**************************************
	 * Method name		: exportExel
	 * Return type		: String
	 * Description		: thuc thi lenh xuat noi dung ra file Exel
	 * Created date		: Jul 27, 2008
	 * Author			: Phonglt
	 **************************************/
	public String exportExel() {
		try {
			if (this.database != null && this.database.size() > 0) {
				ExportExcel.exportJExcel_HaveGroup("quanlydoanra_template",
						"truyvandulieu.xml", "exp_quanlydoanra", this.database,
						UtilsOnScopes.getRequest(), null, null);
				this.setLinkDownload(ExportExcel.pathDownload);
			} else {
				this.setError("Không có dữ liệu được báo cáo.");
				return "func03";
			}
		} catch (Exception e) {
			System.out.println("Lỗi xuất báo cáo.");
			this.setError("Lỗi xuất báo cáo.");
			return "func03";
		}
		return "download";
	}
	
	/**************************************
	 * Method name		: comeback
	 * Return type		: String
	 * Description		: ve lai trang chuc nang truy van du lieu
	 * Created date		: Jul 27, 2008
	 * Author			: Phonglt
	 **************************************/
	public String comeback(){
		return "func03";
	}
}